﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
//using Microsoft.Office.Core;
using Excel;
using ECCSystem.EntityClass;
using ECCSystem.CheckClass;

namespace ECCSystem.BizRule
{
	class ClsExcel
	{
		object objMissing = System.Reflection.Missing.Value;
		string strFilePath = "";
		public ClsExcel(string strPath)
        {
			strFilePath = strPath;
		}

		public string ReadExcel()
		{
			Excel.Application app = new Excel.Application();
			Workbook objWorkBook = null;
			Worksheet objWorkSheet = null;


			objWorkBook = app.Workbooks.Open(strFilePath, objMissing, objMissing, objMissing, objMissing, objMissing,
                                            objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
			int intOrder = 1;
			for (int k = 1; k <= objWorkBook.Sheets.Count; k++)
			{
				intOrder--;
				ArrayList objALKindOfTempletEntity = new ArrayList();
				ArrayList objALEmailTempletEntity = new ArrayList();
				KindOfTempletEntity objKindOfTempletEntity = new KindOfTempletEntity();

				objWorkSheet = (Worksheet)objWorkBook.Sheets[k];
				int intPC = 0;
				int intPhone = 0;
				int intContent = 0;
				int intKeyNo = 0;
				int intkeyName = 0;
				int intClassNo = 0;
				int intClassName = 0;
				

				string strID = "";
				string strKeyName = "";
				int intRows = 2;

				for (int i = 1; i < 26; i++)
				{
					if (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[1, i], objWorkSheet.Cells[1, i]).Value2) == "カテゴリ番号")
					{
						intClassNo = i;
					}
					//objWorkSheet.get_Range(objWorkSheet.Cells[1,i],objWorkSheet.Cells[1,i]).Value2
					if (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[1, i], objWorkSheet.Cells[1, i]).Value2) == "カテゴリ"
							|| clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[1, i], objWorkSheet.Cells[1, i]).Value2) == "分類１")
					{
						intClassName = i;
					}

					if (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[1, i], objWorkSheet.Cells[1, i]).Value2) == "案内文番号")
					{
						intKeyNo = i;
					}

					if (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[1, i], objWorkSheet.Cells[1, i]).Value2) == "案内文名")
					{
						intkeyName = i;
					}

					if (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[1, i], objWorkSheet.Cells[1, i]).Value2) == "PC")
					{
						intPC = i;
					}

					if (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[1, i], objWorkSheet.Cells[1, i]).Value2) == "携帯")
					{
						intPhone = i;
					}

					if (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[1, i], objWorkSheet.Cells[1, i]).Value2) == "交渉記録")
					{
						intContent = i;
					}
				}

				KindOfTempletEntity[] obj = new KindOfTempletEntity[intKeyNo - intClassName - 1];
				int[] intOrderIndex = new int[intKeyNo - intClassName - 1];

				for (int i = 0; i < intOrderIndex.Length; i++)
				{
					intOrderIndex[i] = 0;
				}

				while (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intKeyNo], objWorkSheet.Cells[intRows, intKeyNo]).Value2) != "")
				{
					if (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intClassName], objWorkSheet.Cells[intRows, intClassName]).Value2) != "")
					{
						KindOfTempletEntity objKindOfTempletEntityTmp1 = new KindOfTempletEntity();
						strID = Guid.NewGuid().ToString();
						objKindOfTempletEntityTmp1.KeyID = strID;
						objKindOfTempletEntityTmp1.Name =
							clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intClassName], objWorkSheet.Cells[intRows, intClassName]).Value2);
						strKeyName = objKindOfTempletEntityTmp1.Name;
						objKindOfTempletEntityTmp1.Oreder = 
							Convert.ToDouble(clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intClassNo], objWorkSheet.Cells[intRows, intClassNo]).Value2));
						objKindOfTempletEntityTmp1.ID = 
							Convert.ToString("1A" + clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intClassNo], objWorkSheet.Cells[intRows, intClassNo]).Value2));
						objKindOfTempletEntityTmp1.ParentNode = "1";
						objALKindOfTempletEntity.Add(objKindOfTempletEntityTmp1);
						objKindOfTempletEntity = objKindOfTempletEntityTmp1;
						intOrder++;
					}
					
					for (int i = 0; i < intKeyNo - intClassName - 1; i++)
					{
						if (clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intClassName + 1 + i], objWorkSheet.Cells[intRows, intClassName + 1 + i]).Value2) != "")
						{
							KindOfTempletEntity objKindOfTempletEntityTmp = new KindOfTempletEntity();
							KindOfTempletEntity objEntity = new KindOfTempletEntity();
							if (i == 0)
							{
								objKindOfTempletEntityTmp = objKindOfTempletEntity;
							}
							else
							{ 
								objKindOfTempletEntityTmp = obj[i - 1];
							}

							strID = Guid.NewGuid().ToString();
							objEntity.KeyID = strID;
							objEntity.Name =
								clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intClassName + 1 + i], objWorkSheet.Cells[intRows, intClassName + 1 + i]).Value2);
							strKeyName = objEntity.Name;
							objEntity.Oreder = intOrderIndex[i];
							objEntity.ID = Convert.ToString(objKindOfTempletEntityTmp.ID + "A" + intOrderIndex[i].ToString());
							objEntity.ParentNode = objKindOfTempletEntityTmp.ID;
							objALKindOfTempletEntity.Add(objEntity);
							intOrderIndex[i]++;
							obj[i] = objEntity;
						}
					}

					EmailTempletEntity objEmailTempletEntity = new EmailTempletEntity();
					objEmailTempletEntity.KeyID = strID;
					objEmailTempletEntity.ContentKeyID = Guid.NewGuid().ToString();
					objEmailTempletEntity.AvailabilitySign = 1;
					objEmailTempletEntity.CreateDate = DateTime.Now;
					objEmailTempletEntity.CreatePerson = ClsCommon.gIntOperatorID;
					objEmailTempletEntity.KeyName = strKeyName;
					objEmailTempletEntity.Name =
						clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intkeyName], objWorkSheet.Cells[intRows, intkeyName]).Value2);
					objEmailTempletEntity.Order =
						Convert.ToInt32(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intKeyNo], objWorkSheet.Cells[intRows, intKeyNo]).Value2);
					objEmailTempletEntity.PcContent =
						clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intPC], objWorkSheet.Cells[intRows, intPC]).Value2).Replace("\n", "\r\n");
					objEmailTempletEntity.Negotiation =
						clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intContent], objWorkSheet.Cells[intRows, intContent]).Value2).Replace("\n", "\r\n");
					objEmailTempletEntity.ToteContent =
						clsUICheck.NullToString(objWorkSheet.get_Range(objWorkSheet.Cells[intRows, intPhone], objWorkSheet.Cells[intRows, intPhone]).Value2).Replace("\n", "\r\n");
					objALEmailTempletEntity.Add(objEmailTempletEntity);
					intRows++;
				}
				ClsEmailTemplet objClsEmailTemplet = new ClsEmailTemplet();
				string strRet = objClsEmailTemplet.ImportExcel(objALKindOfTempletEntity, objALEmailTempletEntity);
				if (strRet != "")
				{
					objWorkSheet = null;
					objWorkBook.Close(objMissing, objMissing, objMissing);
					app = null;
					System.GC.Collect();
					return strRet;
				}
				intOrder++;
			}
			
			objWorkSheet = null;
			objWorkBook.Close(objMissing, objMissing, objMissing);
			app = null;
			System.GC.Collect();
			return "";

		}
	}
}
